﻿using lwzlz.Application.Model;
using NPOI.HSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Text;
using System.Web.Mvc;

namespace lwzlz.Web.Areas.Information.Controllers
{
    public class DictionaryController : AdminController
    {
        #region 首页
        // GET: Information/Dictionary
        public ActionResult Index(string table = "", string searchTable = "")
        {
            string sqlConnection = ConfigurationManager.ConnectionStrings["CMS"].ToString();
            var s = "Initial Catalog=";
            int first = sqlConnection.IndexOf("Initial Catalog=") + s.Length;
            int last = sqlConnection.IndexOf("Data Source=") - 1;
            int count = last - first;
            string Catalog = sqlConnection.Substring(first, count);
            //ViewBag.db = "TestOANew";
            ViewBag.db = Catalog;
            ViewBag.Menu = DataTableBll.GetInstance().GetMenu(ViewBag.db, searchTable);//获取该数据库连接字符串中数据库的所有的数据表
            ViewBag.table = table;
            ViewBag.searchTable = searchTable;
            return View();
        }
        #endregion 

        #region 获得表格数据
        [HttpPost]
        public JsonResult ListTB(string db, string table, string rowName = "")
        {
            var count = 0;
            IEnumerable<DataColumnInfo> data = null;
            if (rowName == "" && string.IsNullOrEmpty(table))//获得已选择表的字段数据
            {
                data = null;
            }
            else
            {
                if (!string.IsNullOrEmpty(table))//筛选获得已选择表的字段数据
                {
                    data = DataColumnDal.GetInstance().GetList(db, table, rowName, "sort");
                    count = data.Count();
                }
                else //筛选获得数据库全部的表的字段数据
                {
                    data = DataColumnDal.GetInstance().GetAllList(db, rowName, "sort");
                    count = data.Count();
                }
            }
            return Json(new JsonMsg() { code = 0, msg = "", count = count, data = data }, JsonRequestBehavior.AllowGet);
        }
        #endregion

        #region 删除上传服务器的文件
        [HttpPost]
        public JsonResult DeleteFile(string address = "")
        {
            Directory.Delete(address, true);//删除指定目录下的所有文件
            return Json(new JsonMsg() { code = 0, msg = "", data = "" }, JsonRequestBehavior.AllowGet);
        }
        #endregion

        #region 导出字段信息(新方法)
        [HttpPost]
        public JsonResult ExportNew(string db)
        {
            IEnumerable<DataColumnInfo> data = null;
            var excelAddress = "";
            IEnumerable<TablesName> tbs = DataColumnDal.GetInstance().GetAllTables(db);
            string sExportFileName = "数据库字段信息"; //导出的临时文件的名称
            try
            {
                HSSFWorkbook wb = null;
                try
                {
                    HSSFWorkbook wb1 = new HSSFWorkbook();
                    //HSSFWorkbook wb1 = new HSSFWorkbook();
                    HSSFSheet sheet = (HSSFSheet)wb1.CreateSheet(sExportFileName); //创建工作表

                    int RowNumber = 0;
                    foreach (TablesName obj1 in tbs)
                    {
                        data = DataColumnDal.GetInstance().GetList(db, obj1.TableName);
                        //sheet.CreateFreezePane(0, 1); //冻结列头行
                        HSSFRow TableName = (HSSFRow)sheet.CreateRow(RowNumber); //创建列头行
                        TableName.HeightInPoints = 50F;

                        HSSFCell Table_Title = (HSSFCell)TableName.CreateCell(5); //创建单元格
                        HSSFCellStyle cs_Title1 = (HSSFCellStyle)wb1.CreateCellStyle(); //创建列头样式
                        cs_Title1.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; //水平居中
                        cs_Title1.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; //垂直居中
                        HSSFFont cs_Title_Font1 = (HSSFFont)wb1.CreateFont(); //创建字体
                        cs_Title_Font1.IsBold = true; //字体加粗
                        cs_Title_Font1.FontHeightInPoints = 30; //字体大小
                        cs_Title1.SetFont(cs_Title_Font1); //将字体绑定到样式
                        Table_Title.CellStyle = cs_Title1;
                        Table_Title.SetCellValue(obj1.TableName);

                        RowNumber += 1;
                        HSSFRow row_Title = (HSSFRow)sheet.CreateRow(RowNumber);
                        //row_Title.HeightInPoints = 19.5F; //设置列头行高
                        row_Title.HeightInPoints = 25F;

                        #region 设置列宽
                        for (int i = RowNumber; i < data.Count() + RowNumber; i++)
                        {
                            sheet.SetColumnWidth(i, 25 * 256);
                        }
                        #endregion

                        #region 设置列头单元格样式                
                        HSSFCellStyle cs_Title = (HSSFCellStyle)wb1.CreateCellStyle(); //创建列头样式
                        cs_Title.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; //水平居中
                        cs_Title.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; //垂直居中
                        HSSFFont cs_Title_Font = (HSSFFont)wb1.CreateFont(); //创建字体
                        cs_Title_Font.IsBold = true; //字体加粗
                        cs_Title_Font.FontHeightInPoints = 12; //字体大小
                        cs_Title.SetFont(cs_Title_Font); //将字体绑定到样式
                        #endregion

                        #region 生成列头
                        for (int k = 0; k < 12; k++)
                        {
                            HSSFCell cell_Title = (HSSFCell)row_Title.CreateCell(k); //创建单元格
                            cell_Title.CellStyle = cs_Title; //将样式绑定到单元格
                            switch (k)
                            {
                                case 0:
                                    cell_Title.SetCellValue("字段数");
                                    break;
                                case 1:
                                    cell_Title.SetCellValue("表名");
                                    break;
                                case 2:
                                    cell_Title.SetCellValue("字段名");
                                    break;
                                case 3:
                                    cell_Title.SetCellValue("字段描述");
                                    break;
                                case 4:
                                    cell_Title.SetCellValue("类型");
                                    break;
                                case 5:
                                    cell_Title.SetCellValue("主键");
                                    break;
                                case 6:
                                    cell_Title.SetCellValue("自增长");
                                    break;
                                case 7:
                                    cell_Title.SetCellValue("递增量");
                                    break;
                                case 8:
                                    cell_Title.SetCellValue("长度");
                                    break;
                                case 9:
                                    cell_Title.SetCellValue("小数");
                                    break;
                                case 10:
                                    cell_Title.SetCellValue("可空");
                                    break;
                                case 11:
                                    cell_Title.SetCellValue("标示种子");
                                    break;
                                case 12:
                                    cell_Title.SetCellValue("占用字节数");
                                    break;
                            }
                        }
                        #endregion

                        if (data != null)
                        {
                            foreach (DataColumnInfo obj in data)
                            {
                                #region 设置内容单元格样式
                                HSSFCellStyle cs_Content = (HSSFCellStyle)wb1.CreateCellStyle(); //创建列头样式
                                cs_Content.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; //水平居中
                                cs_Content.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; //垂直居中
                                #endregion

                                #region 生成内容单元格
                                HSSFRow row_Content = (HSSFRow)sheet.CreateRow(RowNumber + 1); //创建行
                                RowNumber++;
                                row_Content.HeightInPoints = 16;
                                for (int j = 0; j < 12; j++)
                                {
                                    HSSFCell cell_Conent = (HSSFCell)row_Content.CreateCell(j); //创建单元格
                                    cell_Conent.CellStyle = cs_Content;
                                    switch (j)
                                    {
                                        case 0:
                                            cell_Conent.SetCellValue(obj.Id);
                                            break;
                                        case 1:
                                            cell_Conent.SetCellValue(obj.TableName);
                                            break;
                                        case 2:
                                            cell_Conent.SetCellValue(obj.Name);
                                            break;
                                        case 3:
                                            cell_Conent.SetCellValue(obj.Description);
                                            break;
                                        case 4:
                                            cell_Conent.SetCellValue(obj.Type);
                                            break;
                                        case 5:
                                            cell_Conent.SetCellValue(obj.IsKeyDesc);
                                            break;
                                        case 6:
                                            cell_Conent.SetCellValue(obj.IsIdentityDesc);
                                            break;
                                        case 7:
                                            cell_Conent.SetCellValue(obj.IdentIncr);
                                            break;
                                        case 8:
                                            cell_Conent.SetCellValue(obj.Length);
                                            break;
                                        case 9:
                                            cell_Conent.SetCellValue(obj.Scale);
                                            break;
                                        case 10:
                                            cell_Conent.SetCellValue(obj.IsNullableDesc);
                                            break;
                                        case 11:
                                            cell_Conent.SetCellValue(obj.IdentSeed);
                                            break;
                                        case 12:
                                            cell_Conent.SetCellValue(obj.Bytes);
                                            break;
                                    }
                                }
                                #endregion
                            }
                        }
                        wb = wb1;
                    }
                }
                catch { }

                var strUploadPath = Server.MapPath("\\bgFile\\File\\excel\\");
                if (!Directory.Exists(strUploadPath))
                {
                    Directory.CreateDirectory(strUploadPath);
                }
                excelAddress = sExportFileName + DateTime.Now.Ticks + ".xls";
                var fileAddress = strUploadPath + excelAddress;
                FileStream file = new FileStream(fileAddress, FileMode.Create);
                wb.Write(file);
                file.Close();
                wb.Close();

            }
            catch { }
            return Json(new JsonMsg()
            {
                code = 0,
                msg = Server.MapPath("\\bgFile\\File\\excel\\"),
                data = excelAddress
            }, JsonRequestBehavior.AllowGet);
        }
        #endregion

        #region 导出字段信息 
        [HttpPost]
        public JsonResult Export(string db, string table, string tables = "")
        {
            //string[] tbs = tables.Split(',');
            IEnumerable<DataColumnInfo> data = null;
            var excelAddress = "";
            if (tables != "")//判断是否是导出多个选项
            {
                IEnumerable<TablesName> tbs = DataColumnDal.GetInstance().GetAllTables(db);
                string sExportFileName = "数据库字段信息"; //导出的临时文件的名称
                try
                {
                    HSSFWorkbook wb = null;
                    try
                    {
                        HSSFWorkbook wb1 = new HSSFWorkbook();
                        int a = 0;
                        foreach (TablesName obj1 in tbs)
                        {
                            data = DataColumnDal.GetInstance().GetList(db, obj1.TableName);
                            //HSSFWorkbook wb1 = new HSSFWorkbook();
                            HSSFSheet sheet = (HSSFSheet)wb1.CreateSheet(obj1.TableName); //创建工作表
                            a++;
                            sheet.CreateFreezePane(0, 1); //冻结列头行
                            HSSFRow row_Title = (HSSFRow)sheet.CreateRow(0); //创建列头行
                            row_Title.HeightInPoints = 19.5F; //设置列头行高

                            #region 设置列宽
                            for (int i = 0; i < data.Count(); i++)
                            {
                                sheet.SetColumnWidth(i, 25 * 256);
                            }
                            #endregion

                            #region 设置列头单元格样式                
                            HSSFCellStyle cs_Title = (HSSFCellStyle)wb1.CreateCellStyle(); //创建列头样式
                            cs_Title.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; //水平居中
                            cs_Title.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; //垂直居中
                            HSSFFont cs_Title_Font = (HSSFFont)wb1.CreateFont(); //创建字体
                            cs_Title_Font.IsBold = true; //字体加粗
                            cs_Title_Font.FontHeightInPoints = 12; //字体大小
                            cs_Title.SetFont(cs_Title_Font); //将字体绑定到样式
                            #endregion

                            #region 生成列头
                            for (int k = 0; k < 12; k++)
                            {
                                HSSFCell cell_Title = (HSSFCell)row_Title.CreateCell(k); //创建单元格
                                cell_Title.CellStyle = cs_Title; //将样式绑定到单元格
                                switch (k)
                                {
                                    case 0:
                                        cell_Title.SetCellValue("字段数");
                                        break;
                                    case 1:
                                        cell_Title.SetCellValue("表名");
                                        break;
                                    case 2:
                                        cell_Title.SetCellValue("字段名");
                                        break;
                                    case 3:
                                        cell_Title.SetCellValue("字段描述");
                                        break;
                                    case 4:
                                        cell_Title.SetCellValue("类型");
                                        break;
                                    case 5:
                                        cell_Title.SetCellValue("主键");
                                        break;
                                    case 6:
                                        cell_Title.SetCellValue("自增长");
                                        break;
                                    case 7:
                                        cell_Title.SetCellValue("递增量");
                                        break;
                                    case 8:
                                        cell_Title.SetCellValue("长度");
                                        break;
                                    case 9:
                                        cell_Title.SetCellValue("小数");
                                        break;
                                    case 10:
                                        cell_Title.SetCellValue("可空");
                                        break;
                                    case 11:
                                        cell_Title.SetCellValue("标示种子");
                                        break;
                                    case 12:
                                        cell_Title.SetCellValue("占用字节数");
                                        break;
                                }
                            }
                            #endregion

                            if (data != null)
                            {
                                int k = 0;
                                foreach (DataColumnInfo obj in data)
                                {
                                    #region 设置内容单元格样式
                                    HSSFCellStyle cs_Content = (HSSFCellStyle)wb1.CreateCellStyle(); //创建列头样式
                                    cs_Content.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; //水平居中
                                    cs_Content.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; //垂直居中
                                    #endregion

                                    #region 生成内容单元格
                                    HSSFRow row_Content = (HSSFRow)sheet.CreateRow(k + 1); //创建行
                                    k++;
                                    row_Content.HeightInPoints = 16;
                                    for (int j = 0; j < 12; j++)
                                    {
                                        HSSFCell cell_Conent = (HSSFCell)row_Content.CreateCell(j); //创建单元格
                                        cell_Conent.CellStyle = cs_Content;
                                        switch (j)
                                        {
                                            case 0:
                                                cell_Conent.SetCellValue(obj.Id);
                                                break;
                                            case 1:
                                                cell_Conent.SetCellValue(obj.TableName);
                                                break;
                                            case 2:
                                                cell_Conent.SetCellValue(obj.Name);
                                                break;
                                            case 3:
                                                cell_Conent.SetCellValue(obj.Description);
                                                break;
                                            case 4:
                                                cell_Conent.SetCellValue(obj.Type);
                                                break;
                                            case 5:
                                                cell_Conent.SetCellValue(obj.IsKeyDesc);
                                                break;
                                            case 6:
                                                cell_Conent.SetCellValue(obj.IsIdentityDesc);
                                                break;
                                            case 7:
                                                cell_Conent.SetCellValue(obj.IdentIncr);
                                                break;
                                            case 8:
                                                cell_Conent.SetCellValue(obj.Length);
                                                break;
                                            case 9:
                                                cell_Conent.SetCellValue(obj.Scale);
                                                break;
                                            case 10:
                                                cell_Conent.SetCellValue(obj.IsNullableDesc);
                                                break;
                                            case 11:
                                                cell_Conent.SetCellValue(obj.IdentSeed);
                                                break;
                                            case 12:
                                                cell_Conent.SetCellValue(obj.Bytes);
                                                break;
                                        }
                                    }
                                    #endregion
                                }
                            }
                        }
                        wb = wb1;
                    }
                    catch { }

                    var strUploadPath = Server.MapPath("\\bgFile\\File\\excel\\");
                    if (!Directory.Exists(strUploadPath))
                    {
                        Directory.CreateDirectory(strUploadPath);
                    }
                    excelAddress = sExportFileName + DateTime.Now.Ticks + ".xls";
                    var fileAddress = strUploadPath + excelAddress;
                    FileStream file = new FileStream(fileAddress, FileMode.Create);
                    wb.Write(file);
                    file.Close();
                    wb.Close();

                }
                catch { }
            }
            else
            {
                data = DataColumnDal.GetInstance().GetList(db, table);
                string sExportFileName = table; //导出的临时文件的名称
                HSSFWorkbook wb = null;
                try
                {
                    try
                    {
                        HSSFWorkbook wb1 = new HSSFWorkbook();
                        HSSFSheet sheet = (HSSFSheet)wb1.CreateSheet(table); //创建工作表
                        sheet.CreateFreezePane(0, 1); //冻结列头行
                        HSSFRow row_Title = (HSSFRow)sheet.CreateRow(0); //创建列头行
                        row_Title.HeightInPoints = 19.5F; //设置列头行高

                        #region 设置列宽
                        for (int i = 0; i < data.Count(); i++)
                        {
                            sheet.SetColumnWidth(i, 25 * 256);
                        }
                        #endregion

                        #region 设置列头单元格样式                
                        HSSFCellStyle cs_Title = (HSSFCellStyle)wb1.CreateCellStyle(); //创建列头样式
                        cs_Title.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; //水平居中
                        cs_Title.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; //垂直居中
                        HSSFFont cs_Title_Font = (HSSFFont)wb1.CreateFont(); //创建字体
                        cs_Title_Font.IsBold = true; //字体加粗
                        cs_Title_Font.FontHeightInPoints = 12; //字体大小
                        cs_Title.SetFont(cs_Title_Font); //将字体绑定到样式
                        #endregion

                        #region 生成列头
                        for (int k = 0; k < 12; k++)
                        {
                            HSSFCell cell_Title = (HSSFCell)row_Title.CreateCell(k); //创建单元格
                            cell_Title.CellStyle = cs_Title; //将样式绑定到单元格
                            switch (k)
                            {
                                case 0:
                                    cell_Title.SetCellValue("字段数");
                                    break;
                                case 1:
                                    cell_Title.SetCellValue("字段名");
                                    break;
                                case 2:
                                    cell_Title.SetCellValue("字段描述");
                                    break;
                                case 3:
                                    cell_Title.SetCellValue("类型");
                                    break;
                                case 4:
                                    cell_Title.SetCellValue("主键");
                                    break;
                                case 5:
                                    cell_Title.SetCellValue("自增长");
                                    break;
                                case 6:
                                    cell_Title.SetCellValue("递增量");
                                    break;
                                case 7:
                                    cell_Title.SetCellValue("长度");
                                    break;
                                case 8:
                                    cell_Title.SetCellValue("小数");
                                    break;
                                case 9:
                                    cell_Title.SetCellValue("可空");
                                    break;
                                case 10:
                                    cell_Title.SetCellValue("标示种子");
                                    break;
                                case 11:
                                    cell_Title.SetCellValue("占用字节数");
                                    break;
                            }
                        }
                        #endregion

                        if (data != null)
                        {
                            int k = 0;
                            foreach (DataColumnInfo obj in data)
                            {
                                if (string.IsNullOrEmpty(obj.Description))
                                {
                                    #region 设置内容单元格样式
                                    HSSFCellStyle cs_Content = (HSSFCellStyle)wb1.CreateCellStyle(); //创建列头样式
                                    cs_Content.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; //水平居中
                                    cs_Content.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; //垂直居中
                                    #endregion

                                    #region 生成内容单元格
                                    HSSFRow row_Content = (HSSFRow)sheet.CreateRow(k + 1); //创建行
                                    k++;
                                    row_Content.HeightInPoints = 16;
                                    for (int j = 0; j < 12; j++)
                                    {
                                        HSSFCell cell_Conent = (HSSFCell)row_Content.CreateCell(j); //创建单元格
                                        cell_Conent.CellStyle = cs_Content;
                                        switch (j)
                                        {
                                            case 0:
                                                cell_Conent.SetCellValue(obj.Id);
                                                break;
                                            case 1:
                                                cell_Conent.SetCellValue(obj.Name);
                                                break;
                                            case 2:
                                                cell_Conent.SetCellValue(obj.Description);
                                                break;
                                            case 3:
                                                cell_Conent.SetCellValue(obj.Type);
                                                break;
                                            case 4:
                                                cell_Conent.SetCellValue(obj.IsKeyDesc);
                                                break;
                                            case 5:
                                                cell_Conent.SetCellValue(obj.IsIdentityDesc);
                                                break;
                                            case 6:
                                                cell_Conent.SetCellValue(obj.IdentIncr);
                                                break;
                                            case 7:
                                                cell_Conent.SetCellValue(obj.Length);
                                                break;
                                            case 8:
                                                cell_Conent.SetCellValue(obj.Scale);
                                                break;
                                            case 9:
                                                cell_Conent.SetCellValue(obj.IsNullableDesc);
                                                break;
                                            case 10:
                                                cell_Conent.SetCellValue(obj.IdentSeed);
                                                break;
                                            case 11:
                                                cell_Conent.SetCellValue(obj.Bytes);
                                                break;
                                        }
                                    }
                                    #endregion
                                }
                            }
                        }
                        wb = wb1;
                    }
                    catch { }
                }
                catch { }

                var strUploadPath = Server.MapPath("\\bgFile\\File\\excel\\");
                if (!Directory.Exists(strUploadPath))
                {
                    Directory.CreateDirectory(strUploadPath);
                }
                excelAddress = sExportFileName + DateTime.Now.Ticks + ".xls";
                var fileAddress = strUploadPath + excelAddress;
                FileStream file = new FileStream(fileAddress, FileMode.Create);
                wb.Write(file);
                file.Close();
                wb.Close();

            }
            return Json(new JsonMsg()
            {
                code = 0,
                msg = Server.MapPath("\\bgFile\\File\\excel\\"),
                data = excelAddress
            }, JsonRequestBehavior.AllowGet);
        }
        #endregion

        #region 显示未描述字段
        public ActionResult Describe(string table = "", string db = "", int tag = 0)
        {
            List<string> zd = new List<string>();
            List<string> zdn = new List<string>();
            List<string> zdc = new List<string>();
            List<string> tbn = new List<string>();
            List<string> zdNull = new List<string>();
            if (tag == 1)
            {
                IEnumerable<DataColumnInfo> data = DataColumnDal.GetInstance().GetAllList(db);
                foreach (DataColumnInfo item in data)
                {
                    if (string.IsNullOrEmpty(item.Description))
                    {
                        zdn.Add("");
                        zd.Add(item.Name);
                        zdc.Add(item.Id);
                        tbn.Add(item.TableName);
                        DataColumnDal.GetInstance().DeleteNull(db, item.Id, item.TableName, item.Name);//清除已添加但是值为空的字段说明
                    }
                }
            }
            else
            {
                IEnumerable<DataColumnInfo> data = DataColumnDal.GetInstance().GetList(db, table);
                foreach (DataColumnInfo item in data)
                {
                    if (string.IsNullOrEmpty(item.Description))
                    {
                        zdn.Add("");
                        zd.Add(item.Name);
                        tbn.Add(item.TableName);
                        zdc.Add(item.Id);
                        DataColumnDal.GetInstance().DeleteNull(db, item.Id, table, item.Name);//清除已添加但是值为空的字段说明
                    }
                }
                ViewBag.table = table;
            }
            ViewBag.zd = zd.ToArray();
            ViewBag.zdn = zdn.ToArray();
            ViewBag.zdc = zdc.ToArray();
            ViewBag.tbn = tbn.ToArray();
            ViewBag.db = db;
            ViewBag.tag = tag;

            return View();
        }
        #endregion

        #region 保存字段解释
        [HttpPost]
        public JsonResult Save(string name = "", string name1 = "", string name2 = "", string table = "", string db = "")
        {
            string[] zdValue = name.Split(',');
            string[] zdCol = name1.Split(',');
            string[] zdTb = name2.Split(',');
            string dbSure = "[" + db + "]";
            var strSql = new StringBuilder(10);
            strSql.AppendFormat("use {0};", dbSure);
            if (name2 != "")
            {
                for (int i = 0; i < zdCol.Length; i++)
                {
                    strSql.AppendFormat("EXECUTE sp_addextendedproperty 'MS_Description', '{0}', 'user', 'dbo', 'table', '{1}', 'column', '{2}' ", zdValue[i], zdTb[i], zdCol[i]);
                }
            }
            else
            {
                for (int i = 0; i < zdCol.Length; i++)
                {
                    strSql.AppendFormat("EXECUTE sp_addextendedproperty 'MS_Description', '{0}', 'user', 'dbo', 'table', '{1}', 'column', '{2}' ", zdValue[i], table, zdCol[i]);
                }
            }
            strSql.ToString();
            DataSet ds1 = null;
            SqlConnection connstring = new SqlConnection(ConfigurationManager.ConnectionStrings["CMS"].ToString());
            SqlCommand cmd = new SqlCommand(strSql.ToString(), connstring);
            using (SqlDataAdapter da = new SqlDataAdapter(cmd))
            {
                ds1 = new DataSet();
                // Fill the DataSet using default values for DataTable names, etc
                da.Fill(ds1);
                connstring.Close();
            }


            return Json(new JsonMsg() { code = 0, msg = "", data = "" }, JsonRequestBehavior.AllowGet);
        }
        #endregion

        #region 单元格编辑
        [HttpPost]
        public void CellEdit(string db = "", string table = "", string colName = "", string value = "", string colId = "")
        {
            //判断是否存在，存在则更新，反之则添加
            DataColumnDal.GetInstance().Edit(db, table, colName, value, colId);
        }
        #endregion

        #region 获得表名注释
        [HttpPost]
        public JsonResult TableName(string db = "", string table = "")
        {
            JsonMsg jm = new JsonMsg();
            jm.msg = "";
            var twoList = DataTableDal.GetInstance().GetList(db, table, 1);
            foreach (DataTableInfo item in twoList)
            {
                jm.msg = item.Description;
            }
            return Json(jm);
        }
        #endregion

        #region 获得表名注释
        [HttpPost]
        public void EditTableName(string db = "", string table = "", string value = "")
        {
            DataColumnDal.GetInstance().EditTableName(db, table, value);
        }
        #endregion

    }
    /// <summary>
    /// 返回与LayUI前端对应的Json数据
    /// </summary>
    public class JsonMsg
    {
        public JsonMsg()
        {
            code = 0;
            msg = "";
        }
        public int code { get; set; }

        public string msg { get; set; }

        public int count { get; set; }
        public dynamic data { get; set; }

    }
}